﻿/* ==============================================================================
* 功能描述：DBManager
* 创 建 者：青金石
* 创建日期：2020/5/28 17:39:01
* ==============================================================================*/

using MySql.Data.MySqlClient;
using System;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;

namespace TCPServer.script.db
{
    public class DBManager
    {
        public static MySqlConnection mysqlConnec;
        private static JavaScriptSerializer Js = new JavaScriptSerializer();

        public static bool Connect(string db, string ip, int port, string user, string pw)
        {
            mysqlConnec = new MySqlConnection();
            // 连接参数
            string s = string.Format("DataBase={0};Data Source={1};port={2};User Id={3};Password={4}", db, ip, port, user, pw);
            mysqlConnec.ConnectionString = s;
            //连接数据库
            try
            {
                mysqlConnec.Open();
                Console.WriteLine("[数据库]connect succ");
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]connect fail," + ex.Message);
                return false;
            }
        }

        // 检测SQL注入
        public static bool IsSafeString(string str)
        {
            return !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\{|\}|%|@|!|\*|']");
        }

        //是否存在该用户
        public static bool isAccountExist(string id)
        {
            //防止SQL注入
            if (!IsSafeString(id))
            {
                return false;
            }
            try
            {
                //查询语句
                string s = string.Format("select * from account where id='{0}';", id);
                MySqlCommand cmd = new MySqlCommand(s, mysqlConnec);
                MySqlDataReader reader = cmd.ExecuteReader();
                bool hasRows = reader.HasRows;
                reader.Close();
                return hasRows;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]IsSafeString err," + ex.Message);
                return false;
            }
        }

        //昵称是否已经存在
        public static bool isNameExist(string name)
        {
            //防止SQL注入
            if (!IsSafeString(name))
            {
                return false;
            }
            try
            {
                //查询语句
                string s = string.Format("select * from player where name='{0}';", name);
                MySqlCommand cmd = new MySqlCommand(s, mysqlConnec);
                MySqlDataReader reader = cmd.ExecuteReader();
                bool hasRows = reader.HasRows;
                reader.Close();
                return hasRows;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]IsSafeString err," + ex.Message);
                return false;
            }
        }

        //注册
        public static bool Register(string id, string pw)
        {
            //防止SQL注入
            if (!IsSafeString(id))
            {
                Console.WriteLine("[数据库]Register fail,id is not safe");
                return false;
            }
            if (!IsSafeString(pw))
            {
                Console.WriteLine("[数据库]Register fail,password is not safe");
                return false;
            }
            //账号是否存在
            if (isAccountExist(id))
            {
                Console.WriteLine("[数据库]Register fail,account is exist");
                return false;
            }
            try
            {
                //对密码进行MD5加密
                string pwMD5 = Utility.MD5Encoding(pw);
                string s = string.Format("insert into account set id='{0}',pw='{1}';", id, pwMD5);
                MySqlCommand cmd = new MySqlCommand(s, mysqlConnec);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]Register fail," + ex.Message);
                return false;
            }
        }

        //创建角色
        public static bool CreatePlayer(string id)
        {
            //防SQL注入
            if (!IsSafeString(id))
            {
                Console.WriteLine("CreatePlayer fail,id is not safe");
                return false;
            }
            PlayerData playerData = new PlayerData();
            string data = Js.Serialize(playerData);
            string sql = String.Format("Insert into player set id='{0}',data='{1}';", id, data);
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnec);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]CreatePlayer err," + ex.Message);
                return false;
            }
        }

        //检查账号密码
        public static bool CheckPassword(string id, string pw)
        {
            //防SQL注入
            if (!IsSafeString(id))
            {
                Console.WriteLine("Login fail,id is not safe");
                return false;
            }
            if (!IsSafeString(pw))
            {
                Console.WriteLine("Login fail,pw is not safe");
                return false;
            }
            string pwMD5 = Utility.MD5Encoding(pw);
            string sql = string.Format("select * from account where id='{0}'and pw='{1}';", id, pwMD5);
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnec);
                MySqlDataReader reader = cmd.ExecuteReader();
                bool hasRows = reader.HasRows;
                reader.Close();
                return hasRows;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]CheckPssword err," + ex.Message);
                return false;
            }
        }

        //获取玩家数据
        public static PlayerData GetPlayerData(string id)
        {
            if (!IsSafeString(id))
            {
                Console.WriteLine("GetPlayerData fail,id is not safe");
                return null;
            }
            string sql = string.Format("select data from player where id='{0}';", id);
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnec);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    reader.Close();
                    return null;
                }
                //读取
                reader.Read();
                string data = reader.GetString("data");
                //反序列化
                PlayerData playerData = Js.Deserialize<PlayerData>(data);
                reader.Close();
                return playerData;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]GetPlayerData err," + ex.Message);
                return null;
            }
        }

        //修改玩家数据
        public static bool UpdatePlayerData(string id, PlayerData data)
        {
            if (!IsSafeString(id))
            {
                Console.WriteLine("UpdatePlayerData fail,id is not safe");
                return false;
            }
            string dataStr = Js.Serialize(data);
            string sql = string.Format("update player set data='{0}' where id='{1}';", dataStr, id);
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnec);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]Update PlayerData err," + ex.Message);
                return false;
            }
        }

        //修改玩家昵称
        public static bool UpdatePlayerName(string id, string name)
        {
            if (!IsSafeString(id))
            {
                Console.WriteLine("UpdatePlayerName fail,id is not safe");
                return false;
            }
            string sql = string.Format("update player set name='{0}' where id='{1}';", name, id);
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mysqlConnec);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("[数据库]GetPlayerName  fail," + ex.Message);
                return false;
            }
        }
    }
}